﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using MapEntities;
using Microsoft.SqlServer.Types;
using System.Collections;
using DALEntities;
using System.Web.UI;
using System.Globalization;

namespace ServerPlatform.DataProvider.Providers
{
    public static class SQLGeoGenerator
    {

        public static void AddPolygonSegment(Shape dest, SqlGeometry g)
        {
            ShapeSegment segment = new ShapeSegment();
            segment.Type = GeometryType.Polygon;

            for (int i = 0; i < g.STNumPoints(); i++)
            {
                SqlGeometry p = g.STPointN(i + 1);
                GeoPoint pt = new GeoPoint(p.STY.Value, p.STX.Value);
                segment.Points.Add(pt);
            }

            dest.Segments.Add(segment);
        }

        public static Shape CreateShapes(SqlGeometry geo, ShapeItemBindingRule rule)
        {
            Shape result = new Shape();
            geo = geo.MakeValid();
            for (int i = 0; i < geo.STNumGeometries(); i++)
            {

                SqlGeometry g = geo.STGeometryN(i + 1);
                if (rule.RadiusFactor.HasValue && rule.RadiusFactor.Value != 0)
                    g = g.Reduce(rule.RadiusFactor.Value);
                if (g.STIsEmpty())
                    continue;
                GeometryType type = GeometryTypeHelper.GetGeometryType(g.STGeometryType().ToString().ToUpperInvariant());
                switch (type)
                {
                    case GeometryType.Polygon:
                        {
                            AddPolygonSegment(result, g);

                            break;
                        }
                    case GeometryType.MultiPolygon:
                        {
                            break;
                        }
                }
            }

            return result;
        }

        public static void AddPolygonSegment(Shape dest, SqlGeography g)
        {
            ShapeSegment segment = new ShapeSegment();
            segment.Type = GeometryType.Polygon;

            for (int i = 0; i < g.STNumPoints(); i++)
            {
                SqlGeography p = g.STPointN(i + 1);
                GeoPoint pt = new GeoPoint(p.Lat.Value, p.Long.Value);
                segment.Points.Add(pt);
            }

            dest.Segments.Add(segment);
        }

        public static Shape CreateShapes(SqlGeography geo, ShapeItemBindingRule rule)
        {
            Shape result = new Shape();

            for (int i = 0; i < geo.STNumGeometries(); i++)
            {

                SqlGeography g = geo.STGeometryN(i + 1);

                if (rule.RadiusFactor.HasValue && rule.RadiusFactor.Value != 0)
                    g = g.Reduce(rule.RadiusFactor.Value);

                if (g.STIsEmpty())
                    continue;
                GeometryType type = GeometryTypeHelper.GetGeometryType(g.STGeometryType().ToString().ToUpperInvariant());
                switch (type)
                {
                    case GeometryType.Polygon:
                        {
                            AddPolygonSegment(result, g);

                            break;
                        }
                    case GeometryType.MultiPolygon:
                        {
                            break;
                        }
                }
            }

            return result;
        }


    }

    public class SQLDataProvider : BaseDataProvider
    {
        public override List<DALEntities.ViewSchema> GetViews()
        {
            return null;
        }

        //private string GetShapeXML(

        public override Shape GetShape(object data, ShapeItemBindingRule rule)
        {
            object geoValue = DataBinder.Eval(data, rule.SpatialField);

            if (geoValue is SqlGeography)
            {
                Shape shape = SQLGeoGenerator.CreateShapes((SqlGeography)geoValue, rule);
                return shape;
            }
            else if (geoValue is SqlGeometry)
            {
                Shape shape = SQLGeoGenerator.CreateShapes((SqlGeometry)geoValue, rule);
                return shape;
            }
            else return null;
        }

        public override DALEntities.ViewResult GetViewResult(DALEntities.ViewRequest request)
        {
            SqlConnection xcon = new SqlConnection(request.ConnectionString);
            xcon.Open();
            SqlCommand xcmd = new SqlCommand();
            xcmd.Connection = xcon;
            xcmd.CommandText = "select * from [" + request.ViewName + "]";
            xcmd.CommandType = CommandType.Text;
            SqlDataAdapter adapter = new SqlDataAdapter(xcmd);
            DataTable table = new DataTable();
            adapter.Fill(table);
            DataView view = table.DefaultView;
            if (request.ExtraData != null && !string.IsNullOrEmpty(request.ExtraData.ToString()))
            {
                view = new DataView(table, request.ExtraData.ToString(), "", DataViewRowState.CurrentRows);
            }
            return new DALEntities.ViewResult() { RequestID = request.ID, Data = (IList)view };
        }

        public override DALEntities.ProviderSource Source
        {
            get { return DALEntities.ProviderSource.MSSQL; }
        }

        private DataTable GetSpecificList(DALEntities.SchemaRequest xrequest, string xcommand, SqlParameter xparams)
        {

            SqlConnection xcon = new SqlConnection(xrequest.ConnectionString);
            xcon.Open();
            SqlCommand xcmd = new SqlCommand();
            xcmd.Connection = xcon;
            xcmd.CommandType = CommandType.StoredProcedure;
            xcmd.CommandText = xcommand;
            xcmd.Parameters.AddWithValue(xparams.ParameterName, xparams.Value);
            SqlDataAdapter xda = new SqlDataAdapter(xcmd);
            DataTable xdt = new DataTable();
            xda.Fill(xdt);
            xcon.Close();
            return xdt;
        }

        private DALEntities.DataType GetDataType(string sqldbtype)
        {
            Dictionary<string, DALEntities.DataType> xtypes = new Dictionary<string, DALEntities.DataType>();
            xtypes.Add("bit", DALEntities.DataType.Unknown);
            xtypes.Add("date", DALEntities.DataType.DateTime);
            xtypes.Add("datetime", DALEntities.DataType.DateTime);
            xtypes.Add("datetime2", DALEntities.DataType.DateTime);
            xtypes.Add("decimal", DALEntities.DataType.Decimal);
            xtypes.Add("float", DALEntities.DataType.Double);
            xtypes.Add("int", DALEntities.DataType.Integer);
            xtypes.Add("money", DALEntities.DataType.Decimal);
            xtypes.Add("nchar", DALEntities.DataType.String);
            xtypes.Add("ntext", DALEntities.DataType.String);
            xtypes.Add("nvarchar", DALEntities.DataType.String);
            xtypes.Add("real", DALEntities.DataType.Double);
            xtypes.Add("smallint", DALEntities.DataType.Integer);
            xtypes.Add("smallmoney", DALEntities.DataType.Decimal);
            xtypes.Add("sql_variant", DALEntities.DataType.Unknown);
            xtypes.Add("text", DALEntities.DataType.String);
            xtypes.Add("bigint", DALEntities.DataType.Int64);
            xtypes.Add("time", DALEntities.DataType.DateTime);
            xtypes.Add("tinyint", DALEntities.DataType.Integer);
            xtypes.Add("smalldatetime", DALEntities.DataType.DateTime);
            xtypes.Add("varchar", DALEntities.DataType.String);
            xtypes.Add("int identity", DALEntities.DataType.Integer);
            xtypes.Add("image", DALEntities.DataType.Blob);
            xtypes.Add("varbinary", DALEntities.DataType.Blob);
            xtypes.Add("binary", DALEntities.DataType.Blob);
            xtypes.Add("uniqueidentifier", DALEntities.DataType.String);
            xtypes.Add("xml", DALEntities.DataType.Unknown);
            xtypes.Add("timestamp", DALEntities.DataType.DateTime);
            xtypes.Add("geography", DALEntities.DataType.Spatial);
            xtypes.Add("geometry", DALEntities.DataType.Spatial);

            if (!xtypes.ContainsKey(sqldbtype))
                return DALEntities.DataType.Unknown;
            else return xtypes[sqldbtype];
        }
        public override DALEntities.ViewSchemaList GetSchema(DALEntities.SchemaRequest request)
        {

            SqlParameter xschemalistparams = new SqlParameter("@table_owner", "dbo");
            DataTable xschemalist = GetSpecificList(request, "sp_tables", xschemalistparams);
            DALEntities.ViewSchemaList xresult = new DALEntities.ViewSchemaList();
            foreach (DataRow xschemaitem in xschemalist.Rows)
            {
                if (xschemaitem["table_name"].ToString() == "dtproperties") continue;
                DALEntities.ViewSchema xtable = new DALEntities.ViewSchema() { Name = xschemaitem["table_name"].ToString(), Description = xschemaitem["table_name"].ToString() };
                SqlParameter xcolumnlistparams = new SqlParameter("@table_name", xschemaitem["table_name"].ToString());
                DataTable xcolumnlist = GetSpecificList(request, "sp_columns", xcolumnlistparams);
                foreach (DataRow xcolumnitem in xcolumnlist.Rows)
                {
                    xtable.Columns.Add(new DALEntities.Column() { Name = xcolumnitem["COLUMN_NAME"].ToString(), Type = GetDataType(xcolumnitem["TYPE_NAME"].ToString()) });
                }
                xresult.Add(xtable);
            }
            return xresult;
        }

        public override void Connect(string connectionString)
        {

        }
    }
}
